package br.com.knowledge.persistence.hibernate;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.io.FileUtils;

import br.com.knowledge.helper.LoadRegexFilters;
import br.com.knowledge.persistence.hibernate.dao.BcdTabelasDAO;
import br.com.knowledge.persistence.hibernate.entities.BcdTabelas;
import br.com.knowledge.persistence.hibernate.entities.CadLinguagem;
import br.com.knowledge.persistence.hibernate.entities.FntFonte;

public class TesteStoredProcedure {
	public static void main(String[] args) throws IOException {
/*		String comando = "Procedure: Upd_S009 Static" + "\n";
		comando = comando + "Parameters" + "\n";
		comando = comando + "   Number: nG003_CdExec" + "\n";
		comando = comando + "   Number: nS009_CdProj" + "\n";
		comando = comando + "   Number: nG022_CdStProjOld" + "\n";
		comando = comando + "   Number: nG022_CdStProjNew" + "\n";
		comando = comando + "   Number: nE001_CdUsuarOld" + "\n";
		comando = comando + "   Number: nE001_CdUsuarNew" + "\n";
		comando = comando + "   String: sS009_ObsStProjOld" + "\n";
		comando = comando + "   String: sS009_ObsStProjNew" + "\n";
		comando = comando + "Local Variables" + "\n";
		comando = comando + "   Sql Handle: hSql" + "\n";
		comando = comando + "   Number: nFetch" + "\n";
		comando = comando + "   Date/Time: d_DateTime" + "\n";
		comando = comando + "Actions" + "\n";
		comando = comando + "   On Procedure Startup" + "\n";
		comando = comando + "      Call SqlConnect(hSql)" + "\n";
		comando = comando + "   On Procedure Execute" + "\n";
		comando = comando + "      Call SqlPrepareAndExecute( hSql, 'Select Distinct" + "\n";
		comando = comando + "	 sysdatetime Into :d_DateTime From SYSTABLES')" + "\n";
		comando = comando + "      Call SqlFetchNext( hSql, nFetch)" + "\n";
		comando = comando + "      If nG022_CdStProjOld!=nG022_CdStProjNew Or sS009_ObsStProjOld!=sS009_ObsStProjNew Or nE001_CdUsuarOld!=nE001_CdUsuarNew" + "\n";
		comando = comando + "	 Call SqlPrepareAndExecute( hSql, 'Insert Into S014_ProjStHist(" + "\n";
		comando = comando + "	    G003_CdExec,S009_CdProj,S014_DtStHist,G022_CdStProj,E001_CdUsuar,S014_Obs)" + "\n";
		comando = comando + "	    Values (" + "\n";
		comando = comando + "	    :nG003_CdExec,:nS009_CdProj,:d_DateTime,:nG022_CdStProjOld," + "\n";
		comando = comando + "	    :nE001_CdUsuarOld,:sS009_ObsStProjOld )')" + "\n";
		comando = comando + "   On Procedure Close" + "\n";
		comando = comando + "      Call SqlDisconnect(hSql)" + "\n";
*/

		String comando = "Procedure: Upd_S009 Static" + "\n";
		comando = comando + "Call SqlPrepareAndExecute( hSql, '" + "\n";
		comando = comando + "Insert Into" + "\n";
		comando = comando + "s084_contratoimp" + "\n";
		comando = comando + "( " + "\n";
		comando = comando + "s084_cd, " + "\n";
		comando = comando + "s005_cdcontrempres, " + "\n";
		comando = comando + "s084_dtcria, " + "\n";
		comando = comando + "e001_cdusuarcria, " + "\n";
		comando = comando + "s084_txt, " + "\n";
		comando = comando + "g077_cd" + "\n";
		comando = comando + ") values (" + "\n";
		comando = comando + ":ns084_cd, " + "\n";
		comando = comando + ":frmContratoAg.dfS005Ag_S005_CdContrEmpres, " + "\n";
		comando = comando + ":dtNow, " + "\n";
		comando = comando + ":frmContratoAg.cv_E001_CdUsuar, " + "\n";
		comando = comando + ":lsVariaveis, " + "\n";
		comando = comando + ":nG077_cd" + "\n";
		comando = comando + ")')" + "\n";
		comando = comando + "Call SqlFetchNext( hSql, nFetch)" + "\n";
		comando = comando + "Call SqlPrepareAndExecute( hSql, 'Insert Into S008_ContrCapEndiv ( G003_CdExec, G006_CdCapEndiv, S005_CdContrEmpres, S008_VrCompr) " + "\n";
		comando = comando + "Values" + "\n";
		comando = comando + "(:frmContratoAg.dfS005Ag_G003_CdExec, :frmContratoAg.tblS005_S008_ContrCapEndiv.colS008Ag_G006_CdCapEndivi, :frmContratoAg.dfS005Ag_S005_CdContrEmpres, :frmContratoAg.tblS005_S008_ContrCapEndiv.colS008Ag_S008_VrCompri )')" + "\n";
		comando = comando + "Call SqlFetchNext( hSql, nFetch)" + "\n";

		comando = comando + "Call SqlPrepareAndExecute( hSql, 'Update S005_ContrEmprest" + "\n";
		comando = comando + "Set" + "\n";
		comando = comando + "S001_CdConv = :dfS005Ag_S001_CdConv," + "\n";
		comando = comando + "G003_CdExec=:dfS005Ag_G003_CdExec," + "\n";
		comando = comando + "E001_CdUsuarEmis = :dfS005Ag_E001_CdUsuarEmis," + "\n";
		comando = comando + "E001_CdUsuarHomo= :dfS005Ag_E001_CdUsuarHomo," + "\n";
		comando = comando + "E001_CdUsuarAtiv= :dfS005Ag_E001_CdUsuarAtiv," + "\n";
		comando = comando + "E001_CdUsuar= :dfS005Ag_E001_CdUsuar," + "\n";
		comando = comando + "G008_CdTpProg= :dfS005Ag_G008_CdTpProg," + "\n";
		comando = comando + "S005_DtAssinatura= :dfS005Ag_S005_DtAssinatura," + "\n";
		comando = comando + "S005_DtAssOrig= :dfS005Ag_S005_DtAssinatura," + "\n";
		comando = comando + "S005_PzAmort= :dfS005Ag_S005_PzAmort," + "\n";
		comando = comando + "S005_PzCarencia= :dfS005Ag_S005_PzCarencia," + "\n";
		comando = comando + "S005_InRegCart= :cbS005Ag_S005_InRegCart," + "\n";
		comando = comando + "S005_VrParcela= :dfS005Ag_S005_VrParcela," + "\n";
		comando = comando + "G026_CdBanco= :dfS005Ag_G026_CdBanco," + "\n";
		comando = comando + "G043_CdIndEconomic= :dfS005Ag_G043_CdIndEconomic," + "\n";
		comando = comando + "S005_DiaBase= :dfS005Ag_S005_DiaBase," + "\n";
		comando = comando + "G045_CdTxJuro= :dfS005Ag_G045_CdTxJuro," + "\n";
		comando = comando + "S005_VrBloqueado= :dfS005Ag_S005_VrBloqueado," + "\n";
		comando = comando + "S005_TpAmort= :dfS005Ag_S005_TpAmort," + "\n";
		comando = comando + "S005_AnoContrEmpre= :dfS005Ag_S005_AnoContrEmpre," + "\n";
		comando = comando + "S005_DtCriacao= :dfS005Ag_S005_DtCriacao," + "\n";
		comando = comando + "S005_InHomolog= :cbS005Ag_S005_InHomolog," + "\n";
		comando = comando + "S005_CdBacen= :dfS005Ag_S005_CdBacen," + "\n";
		comando = comando + "S005_CdArqBC= :dfS005Ag_S005_CdArqBC," + "\n";
		comando = comando + "S005_Status= :dfS005Ag_S005_Status," + "\n";
		comando = comando + "S005_DiaVenc= :dfS005Ag_S005_DiaVenc," + "\n";
		comando = comando + "S005_DtFim = :dfS005Ag_S005_DtFim," + "\n";
		comando = comando + "G026_CdBancoPag= :dfS005Ag_G026_CdBancoPag," + "\n";
		comando = comando + "F026_CdExecCtaCobr =:dfS005Ag_F026_CdExecCtaCobr," + "\n";
		comando = comando + "F026_CdExecCtaGara =:dfS005Ag_F026_CdExecCtaGara," + "\n";
		comando = comando + "S005_VrTotal  = :dfS005Ag_S005_VrParcela" + "\n";
		comando = comando + "Where S005_CdContrEmpres=:dfS005Ag_S005_CdContrEmpres')" + "\n";

		//File codigoFonte = new File("E:\\WTF\\desenvolvimento\\sam\\prc_025.apt");
		File codigoFonte = new File("E:\\WTF\\desenvolvimento\\sam\\prc_025.apt");
		//File codigoFonte = new File("E:\\WTF\\prc_025.apt");
		comando = FileUtils.readFileToString(codigoFonte, "UTF-8");
		
		String sBuffer = null;
		String sTabela = null;
		String sBufferColunas = null;
		String [] tokens = null;
		String pattern = "(SqlPrepareAndExecute\\()\\s([a-zA-Z0-9_]+)(.(?!Break)(?!Call)(?!Else)(?!Else If)(?!If)(?!Loop)(?!Return)(?!select Case)(?!When)(?!While)(?!On\\s)(?!Form Windown)(?!MDI Window)(?!Table Window))+";
		String patternInsert = "(Insert)\\s([a-zA-Z0-9_]+)(.(?!Break)(?!Call)(?!Else)(?!Else If)(?!If)(?!Loop)(?!Return)(?!select Case)(?!When)(?!While)(?!On\\s)(?!Form Windown)(?!MDI Window)(?!Table Window))+";
		String patternSelect = "(Select)\\s([a-zA-Z0-9_]+)(.(?!Break)(?!Call)(?!Else)(?!Else If)(?!If)(?!Loop)(?!Return)(?!select Case)(?!When)(?!While)(?!On\\s)(?!Form Windown)(?!MDI Window)(?!Table Window))+";
		String patternUpate = "(Update)\\s([a-zA-Z0-9_]+)(.(?!Break)(?!Call)(?!Else)(?!Else If)(?!If)(?!Loop)(?!Return)(?!select Case)(?!When)(?!While)(?!On\\s)(?!Form Windown)(?!MDI Window)(?!Table Window))+";
		String patternDelete = "(Delete)\\s([a-zA-Z0-9_]+)(.(?!Break)(?!Call)(?!Else)(?!Else If)(?!If)(?!Loop)(?!Return)(?!select Case)(?!When)(?!While)(?!On\\s)(?!Form Windown)(?!MDI Window)(?!Table Window))+";
		Pattern MY_PATTERN = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
		Matcher matcherTrigger = MY_PATTERN.matcher(comando);
		int nPosicao=0; 
		while(matcherTrigger.find()){
			sBuffer = matcherTrigger.group(0);
			System.out.println("\n\n- - - - - - - - - - - - - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - \n\n");
			System.out.println("\nProcedure: " + sBuffer);
			nPosicao++;
			//sBuffer = sBuffer.replaceAll("\t|\n|\r|\\s+", " ");
			
			MY_PATTERN = Pattern.compile( patternInsert, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
			Matcher matcherComando = MY_PATTERN.matcher(sBuffer);
			if (matcherComando.find()){
				sBuffer = matcherComando.group(0);
				sBuffer = sBuffer.toLowerCase();
				sTabela = sBuffer.substring(sBuffer.indexOf("into")+4, sBuffer.indexOf("("));
				sTabela = sTabela.replace("\\s", "");
				sBufferColunas = sBuffer.substring(sBuffer.indexOf("(")+1, sBuffer.indexOf(")"));
				tokens = sBufferColunas.split(",");
				System.out.println("Comando: INSERT");				
				System.out.println("Tabela: " + sTabela);
				for(String sColuna:tokens ){
					if(sColuna.isEmpty()==false){
						System.out.println("Colunas: " + sColuna);
					}					
				}				
			}
			else{				
				MY_PATTERN = Pattern.compile( patternUpate, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
				matcherComando = MY_PATTERN.matcher(sBuffer);
				if (matcherComando.find()){
					sBuffer = matcherComando.group(0);
					sBuffer = sBuffer.toLowerCase();
					sTabela = sBuffer.substring(sBuffer.indexOf("update")+6, sBuffer.indexOf("set"));
					sTabela = sTabela.replace("\\s", "");
					sBufferColunas = sBuffer.substring(sBuffer.indexOf("set")+3, sBuffer.indexOf("where")-5);
					tokens = sBufferColunas.split(",");
					System.out.println("Comando: UPDATE");
					System.out.println("Tabela: " + sTabela);
					nPosicao =0;
					List<String> sArrayColunas = new ArrayList<String>(); 
					for(String sColuna:tokens ){
						if(sColuna.isEmpty()==false){
							sArrayColunas.add(sColuna.substring(0, sColuna.indexOf("=")));
						}			
					}
					for(String sColuna:sArrayColunas ){
						if(sColuna.isEmpty()==false){
							System.out.println("Colunas: " + sColuna);
						}			
					}					
				}
				else{
					MY_PATTERN = Pattern.compile( patternDelete, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
					matcherComando = MY_PATTERN.matcher(sBuffer);
					if (matcherComando.find()){
						sBuffer = matcherComando.group(0);
						sBuffer = sBuffer.toLowerCase();
						sTabela = sBuffer.substring(sBuffer.indexOf("delete")+4, sBuffer.indexOf("from"));
						sTabela = sTabela.replace("\\s", "");
						System.out.println("Comando: DELETE");				
						System.out.println("Tabela: " + sTabela);						
					}
					else{
						MY_PATTERN = Pattern.compile( patternSelect, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
						matcherComando = MY_PATTERN.matcher(sBuffer);
						if (matcherComando.find()){
							sBuffer = matcherComando.group(0);
							sBuffer = sBuffer.toLowerCase();
							List<String> sArrayColunas = new ArrayList<String>();
							List<String> sArrayTabelas = new ArrayList<String>();
							String patternSelectColumn = "(Select)\\s([a-zA-Z0-9_]+)(.(?!into)(?!from))+";
							MY_PATTERN = Pattern.compile( patternSelectColumn, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
							Matcher matcherSelectColumn = MY_PATTERN.matcher(sBuffer);
							while(matcherSelectColumn.find()){
								sBufferColunas = matcherSelectColumn.group(0);
								sBufferColunas = sBufferColunas.replaceAll("select", "");
								tokens = sBufferColunas.split("(!?,)(!?+)(!?|)(!?-)");
								for(String sColuna:tokens ){
									if(sColuna.isEmpty()==false){
										if(sColuna.indexOf("(")!=-1){
											sColuna=sColuna.substring(sColuna.indexOf("(")+1, sColuna.indexOf(")"));
										}
										sArrayColunas.add(sColuna);
									}			
								}
							}

							String patternSelectTables = "(from)\\s([a-zA-Z0-9_]+)(.(?!into)(?!where)(?!group by)(?!having)(?!order by))+";
							MY_PATTERN = Pattern.compile( patternSelectTables, Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE );
							Matcher matcherSelectTables = MY_PATTERN.matcher(sBuffer);
							while(matcherSelectTables.find()){
								sTabela = matcherSelectTables.group(0);
								sTabela = sTabela.replaceAll("from", "" );
								tokens = sTabela.split(",");
								for(String sTable:tokens ){
									if(sTable.isEmpty()==false){
										sArrayTabelas.add(sTable);
									}			
								}
							}
							System.out.println("Comando: SELECT");
							System.out.println("Tabela: ");
							for(String sTable:sArrayTabelas ){
								if(sTable.isEmpty()==false){
									System.out.println(sTable);
								}			
							}
							System.out.println("Coluna: ");
							for(String sColuna:sArrayColunas ){
								if(sColuna.isEmpty()==false){
									System.out.println(sColuna);
								}			
							}							
						}
						else{
							System.out.println("COMANDO IGNORADO");
						}
					}					
				}				
			}
		}		
		System.out.println("\nSqlPrepareAndExecute : " + String.valueOf(nPosicao) );
	}
	
}
